home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Shareware Grab Bag
/
Shareware Grab Bag.iso
/
003
/
db3n8510.arc
/
DB3N8510.TXT
Wrap
Text File
|
1985-10-25
|
50KB
|
1,328 lines
dBASE III Anomalies and Workarounds
1.1 How to use this Section
1.2 @...GET alias to @...SAY...PICTURE
>>> @...GET alias in Format File
Any command that allows editing of a record will only operate in the
SELECTed work area. However, attempting an @...SAY...GET using an ALIAS or
SELECT to access another work area in a format (.FMT) file will not produce
an error message. Instead, the entire line is suppressed, including the SAY
statement. The Developer's Release of dBASE III will return the "Variable
not found" error message if any attempt is made to GET a field from another
work area. In versions 1.0 and 1.1 of dBASE III, the command:
@ 10,4 SAY "Testing " GET B->Test
in a format file will not display anything on the screen. Issuing this
command from the dot prompt or in a command file will return "Variable not
found." An alias name is acceptable in a SAY statement, even in a format
file.
@ 10,4 SAY "Testing " + B->Test
will display properly.
Notice that in the first example neither the SAY nor the GET pertaining to
the second work area are displayed.
[1.0, 1.1, D.R.]
>>> @...SAY...PICTURE "@("
Displaying negative numeric memory variables with the @...SAY command and
the PICTURE function "@(" to enclose negative numbers in parentheses, will
not display the PICTURE correctly. Blanks will display between the
beginning parenthesis and the memory variable contents. For example:
number = 23.15
@ 10,10 SAY number PICTURE "@("
( 23.15)
The workaround is as follows:
STORE STR( number, 12, 2 ) TO mem1
^------------ Use an appropriate length
and decimal value.
STORE AT( "-", mem1 ) TO mpos
@ 10,10 SAY SPACE(mpos-1) + "(" + SUBSTR(mem1,mpos+1) + ")"
(23.15)
[1.0, 1.1, D.R.]
>>> @...SAY...PICTURE "$", ",", and "9"
Using the template symbols "$", ",", and "9" in an @...SAY PICTURE clause
may cause more than one dollar sign to be displayed on the screen.
Specifically, if the value being displayed does not fill positions in the
display string preceding the comma position, a dollar sign will be
displayed in place of the comma.
For example:
mem = 123456
@ 10,0 SAY mem PICTURE "$999,999,999"
@ 11,0 SAY mem PICTURE "$999,999,999,999"
will output:
$ $123,456
$ $ $123,456
The PICTURE template, ($), is intended to replace leading zeros in a
numeric display with dollar signs. This means that dollar signs should
always display in a fixed position format. To display a fixed position
dollar sign leading a numeric expression with embedded commas, use two
@...SAY commands, one to display the dollar sign and the other to display
the numeric variable with the embedded commas. For example,
mem = 1234.56
@ 10,10 SAY "$"
@ ROW(),COL() + 1 SAY mem PICTURE "99,999.99"
If you wish a leading dollar sign that is floating for numeric variables
with embedded commas, a feature not directly supported by dBASE III, use
the following command file to format the numeric variables.
* Commas.PRG
PARAMETERS mem
mvar = SUBSTR( STR( mem, 9, 2 ), 1, 3 ) + ",";
+ SUBSTR( STR( mem, 9, 2 ), 4, 3 );
+ SUBSTR( STR( mem, 9, 2 ), 7, 3 )
cntr = 1
DO WHILE SUBSTR( mvar, cntr, 1 ) $ ", "
cntr = cntr + 1
ENDDO
cnum = SPACE( cntr - 1 ) + "$" + SUBSTR( mvar, cntr )
@ 10,0 SAY cnum
* EOP Commas.PRG
STORE 1234.56 TO x
DO Commas WITH x
outputs:
$1,234.56
[1.0, 1.1, D.R.]
This formula will work with numbers as large as $999,999.99. Larger numbers
require that the length argument of the STR() function and the length and
starting point arguments of the SUBSTR() function be changed to accommodate
the larger number. For numbers as large as $999,999,999.99 the second line
of the preceding program should be changed to:
mvar = SUBSTR( STR( mem, 12, 2 ), 1, 3 ) + ",";
+ SUBSTR( STR( mem, 12, 2 ), 4, 3 ) + "," ;
+ SUBSTR( STR( mem, 12, 2 ), 7, 3 ) +;
SUBSTR( STR( mem, 12, 2 ), 10, 2 )
This problem persists in the Developer's Release including both the PICTURE
clause and the TRANSFORM() function. The work-arounds are a little bit
easier given some new functions including TRANSFORM() itself. To display a
numeric expression with a leading dollar sign in fixed position and
embedded commas concatenate a dollar sign to the result of the TRANSFORM()
of the numeric expression. For example,
mem = 1234.56
@ 10,10 SAY "$" + TRANSFORM( mem, "99,999.99" )
To display a floating dollar sign leading a numeric expression with
embedded commas, insert the dollar sign into the result of the TRANSFORM()
function on the numeric expression. For example,
mem = 1234.56
@ 10,10 SAY SPACE(10-LEN(LTRIM(TRANSFORM(mem,"99,999.99"))));
^--------- Length of the TRANSFORM() picture.
+ "$" + TRANSFORM(mem,"99,999.99")
It is important to note that the first element in the SPACE() function
argument must be the length of the TRANSFORM() picture. If, for example,
the picture is "999", then the element is three.
>>> @...SAY...PICTURE "@X" and "@C"
The C and X functions documented in the manual reference to the @...SAY
command will always display positive numbers as credits (CR) and negative
numbers as debits (DB). Use the following program segment if you need them
to display in the reverse order (that is, positive numbers as debits (DB)
and negative numbers as credits (CR)):
DO CASE
CASE number > 0
@ row,col SAY STR(number,17,2) + "DB"
CASE number < 0
@ row,col SAY STR(-number,17,2) + "CR"
OTHERWISE
@ row,col SAY STR(number,17,2)
ENDCASE
[1.0, 1.1, D.R.]
>>> @...SAY...PICTURE "@A" and "@!"
The A and ! PICTURE clause functions are mutually exclusive. That is, you
cannot combine the two to make a new function that will limit data input to
alphabetic characters and force the letters to uppercase. If A and ! are
used together, only the second function will be in effect. For example:
PICTURE "@A!" is equivalent to PICTURE "@!"
PICTURE "@!A" is equivalent to PICTURE "@A"
[1.0, 1.1, D.R.]
>>> @...SAY...PICTURE Logical Variable
There is no PICTURE clause that applies to a logical variable in an @...GET
command. However, if you attempt to use a PICTURE clause on a logical
field or memory variable, dBASE III will not trap it as an error. When the
READ is executed, dBASE III will not pause for user input, and the value of
the field or variable will not be changed.
One possible occurrence of this is when a memory variable is declared
PUBLIC but is not initialized before an @...GET command. (dBASE III will
automatically initialize a PUBLIC memory variable to a logical .F.) If the
variable was not intended to be a logical variable, the above symptoms will
manifest themselves. You may want to use the SPACE() function to initialize
character variables and zero to initialize numeric variables.
[1.0, 1.1, D.R.]
>>> @...SAY...PICTURE "@Z"
The zero-suppress function, @Z, will not suppress the decimal point when it
is used with a non-integer value of zero. To work around this display
problem, use the following command sequence instead of the @Z function.
IF STR( number, 5, 2 ) <> " 0.00"
@ 10,0 SAY number
ENDIF
[1.0, 1.1]
1.3 APPEND to CONFIG.DB
>>> CONFIG.DB with SCOREBOARD
The entry SCOREBOARD=OFF has no effect if set in CONFIG.DB. SETing the
SCOREBOARD OFF suppresses the display of status and error messages to line
0 . The SCOREBOARD setting can only be changed from a command file or the
dot prompt with the command SET SCOREBOARD OFF.
1.4 COPY [STRUCTURE] to DO WHILE
1.5 EDIT to LABEL limitations
>>> EDIT
If BROWSE is used on an indexed file and is terminated with a Ctrl-Q or
Ctrl-W and followed with an EDIT command, keys which advance or regress
through the database file (PgUp, PgDn, uparrow, downarrow, Ctrl-E, Ctrl-R,
Ctrl-C) will not work properly.
Sometimes these keys will drop the user out of EDIT to the dot prompt.
Other times they will move two or three records instead of one. The
display will sometimes lock on the current record. These keys may also
cause the pointer to be positioned at the next to last record in the index.
[1.0, 1.1, D.R.]
>>> INKEY()
The INKEY() function of the Developer's Release does not always read the
leftarrow key as CHR(19). Running the following program will demonstrate
that dBASE III will trap this key only rarely.
i = 0
DO WHILE i <> 13
i = INKEY()
? i
ENDDO
When you run this program, you will notice that dBASE III treats the
leftarrow key as a Ctrl-S, and will pause or start scrolling accordingly.
Sometimes the key will be trapped and INKEY() will return 19; however, this
is far less common than the former result.
[D.R.]
1.6 MODIFY STRUCTURE to PCOL() and SET MARGIN TO
1.7 RECNO() to ROUND()
1.8 SET <full-screen> to SORT
>>> SET RELATION TO
The command syntax SET RELATION INTO <file> TO <key> will not SET the
RELATION although no error message is displayed and DISPLAY STATUS shows
the RELATION as SET.
The SET RELATION command is sensitive to the order that the INTO and the TO
clauses are specified. Apparently, everything after the INTO clause is
ignored. Be sure to check that you have specified the TO clause before the
INTO clause if your RELATIONs appear to fail.
[1.0, 1.1]
>>> SORT
(1) Attempting to SORT a file with SET FILTER TO SUBSTR(fieldname,n,n) =
<value> will return a "*** STACK OVERFLOW ***" and drop you out of dBASE
III to the operating system.
(2) SORTing a database file from a command file that is nested 13 levels
deep will fail with a system crash. The SORT command will return a status
message stating that a percentage of the records in the original file were
SORTed, but then the computer will freeze with no error message or warning.
(3) Attempting to use the command SORT TO <Filename> ON <Fieldname> with a
RELATION SET produces the error message "*** STACK OVERFLOW ***" and will
drop you out of dBASE III to the operating system prompt. This will not
occur if the RELATION is SET to RECNO().
(4) One cannot SORT to a file in a directory other than the current one.
An attempt to do this will not produce an error message and will not create
the designated file. Instead, a file name W44 will be created in the
current directory of the default drive.
(5) The effective limit of SORT is in the 32,000 record range. SORTing a
file with more than 32,000 records will produce the following error
messages:
nn% Sorted
Records do not balance...(PROGRAM ERROR)
100% Sorted
The SORTed file will not contain all the records from the original.
The workaround for sorting databases in excess of 32,000 records is to
INDEX and then COPY. The procedure is as follows:
USE <filename>
INDEX ON <sort key> to Temp
COPY TO Sorted
[1.0, 1.1]
>>> SORT with 66 records
SORTing a file that has more than 66 records in such a way that the
resulting file has exactly 64 records will produce a file in which the
first record is corrupted.
[1.0, 1.1]
1.9 STORE to ZAP
>>> USE
An attempt to use the PC/MS-DOS directory navigation path (..) in a USE
command will give unexpected results. USEing the database will make the
file active, but there will be no ALIAS. An attempt to USE another
database file in another SELECT area using the same technique, will cause
the error message "ALIAS name already in use" to be displayed.
* ---Open first database file.
USE ..\nme\Budget
DISPLAY STATUS
Currently selected database:
Select area - 1, Database in use: C:..\nme\Budget.dbf
Alias -
* ---Open another database file.
SELECT 2
USE ..\nme\Sumfrm
ALIAS name already in use
?
USE ..\nme\sumfrm
If an ALIAS is assigned in both USE commands, no error results. If you need
to avoid specifying the full path name, use the RUN command to log the
previous directory and then specify the datafile to USE. For example,
RUN CD ..
USE nme\Budget
SELECT 2
USE nme\Sumfrm
[1.0, 1.1, D.R]
1 dBASE III Programming
1.1 Program documentation
1.2 Database file structure
1.3 Debugging - program break points
1.4 dFORMAT
1.5 MEMO fields
1.6 Setting the system date and time
1.7 Recreating a corrupted dBASE III header
1.8 Simulating the JOIN command
1.9 Limitations
1.10 Capatilizing
1.11 Left-justifying Character Fields
2 dBASE III Frequently Asked Questions
2.1 Installation
2.2 Commands
2.3 New data types
2.4 Memory variables
2.5 Printing
2.6 Data transfer
3 dBASE III Reference
3.1 @...GET to Boolean Operators
>>> @...GET PICTURE "@B"
The function "@B" is used with the @...SAY...GET statement to left-justify
variables of numeric type. It is most useful when SAYing a numeric field
or memory variable that is more easily understood as a character string,
such as a part number. Use of this FUNCTION with GET, however, causes a
slight change in the way numeric variables are read from the screen that
may cause some difficulties.
A numeric memory variable will default to a length of ten digits when
initialized; however, if you are using the PICTURE function "@B" in an
@...GET statement, a numeric memory variable will GET the width of the
memory variable exactly as initialized, even if a template symbol is used.
Initializing a memory variable to zero will cause the GET statement to
display one zero on the screen. A READ command will allow one digit only
to be entered to the memory variable.
This occurs whether the memory variable is initialized to 0 or 0000. For
example:
SET DELIMITERS ON
w = 1234
x = 0
y = 0000
@ 9,0 GET w PICTURE "@B9999"
@ 10,0 GET x PICTURE "@B9999"
@ 11,0 GET y PICTURE "@B9999"
will produce:
:1234:
:0:
:0:
A READ command will allow four characters to be entered in the first
variable, but only one character in the next two variables. If the "@B"
function is used, initialize the memory variable to the proper length or
the input may be truncated.
3.2 CHR() to FILE() function
>>> Demonstration Disk (Runtime+)
In the Developer's Release of dBASE III, the dBRUN programs from the
Demonstration Disk are not compatible with the full system.
Code crunched with DBC.COM from the Demonstration Disk can only be run with
dBRUN from the Demonstration Disk. Code crunched with DBC.COM from the
Developer's Disk can only be run with the full dBASE III system or the
dBRUN disk, purchased separately.
The error message:
No database is in USE. Enter filename:
is a common indicator that the incorrect dBRUN or dBC is being used.
3.3 FIND to MODIFY STRUCTURE
3.4 Numeric fields to PARAMETERS
3.5 PRIVATE to PROW()
>>> PROW()
When issuing SET PRINT ON, PROW() will be set to 2, regardless of its
previous setting. An EJECT will reset PROW() to 2, not 0. Issuing an EJECT
with SET PRINT OFF will reset PROW() to 0.
When SET PRINT is ON, PROW() may never equal 0 or 1. Any attempt to test
for PROW() = 0 or PROW() = 1 will work only when SET PRINT is OFF.
3.6 PUBLIC to REPORT FORM
3.7 RELEASE to SET PROCEDURE
>>> SET COLOR TO ,<cr>
The command:
SET COLOR TO ,<cr>
will produce black on black, even if there is no space after the comma.
3.8 SET RELATION to warnings
3.9 dBase III File Structure
3.10 dBASE III Memo File Structure
3.11 Installation and Configuration
4 dBASE III Sample Programs
4.1 Left Justifying Character Fields
by Stanley Ballenger
Converting a numeric field to a character field using MODIFY STRUCTURE will
leave the character field right-justified. If you find that you need the
new character field to be left-justified, the following procedure will be a
welcome addition to your procedure library.
This procedure takes any database file and left-justifies all of the
character fields in each record, trimming all the leading blanks. It uses
an EXTENDED STRUCTURE to hold the names of all the character fields. Then,
taking each character field in turn, it passes through the entire database
file left-justifying that character field's values. The next character
field is read from the EXTENDED STRUCTURE and the process is repeated.
This proceeds until there are no more character fields to left-justify.
Note that this procedure makes as many passes through your database file as
there are character fields and, as such executes quite slowly.
* Program....: Ljustify.PRG
* Author.....: Stanley Ballenger
* Date. .....: July 1, 1985
* Notes......: Left-justifies all character type fields in a
* database file.
*
PRIVATE start, end, string, fname
* ---Open files.
SELECT 1
USE Yourfile
COPY STRUCTURE EXTENDED TO Temp
SELECT 2
USE Temp
SET FILTER TO Field_type = "C"
GO TOP
CLEAR
* ---Set up field count and display it.
fldcount = 0
column = COL() + 10
@ ROW(),column SAY STR( fldcount,10 ) + " Field values replaced"
* ---Justify character fields.
DO WHILE .NOT. EOF()
fname = Field_name
SELECT Yourfile
GO TOP
end = LEN( &fname )
* ---Remove leading blanks for the current field.
DO WHILE .NOT. EOF()
start = 1
string = &fname
* ---Remove leading blanks from cu~rrrent field.
DO WHILE SUBSTR( string,start,1 ) = " " .AND. start < end
start = start + 1
ENDDO
* ---Replace field if it is left-justified.
IF start <> 1
REPLACE &fname WITH SUBSTR( string,start,end )
fldcount = fldcount + 1
@ ROW(),c~orlumn SAY STR( fldcount,10 )
ENDIF
* ---Get next record.
SKIP
ENDDO
SELECT Temp
* ---Get next character field name.
SKIP
ENDDO
* ---Clean up.
CLOSE DATABASES
ERASE Temp.DBF
RETURN
* EOF Ljustify.PRG
4.2 Creative Uses of Database Files for Reporting
Database files are central to dBASE II and dBASE III. They are the
repositories of the information we want to record and access. Typical uses
of database files are mailing lists, customer lists, records of purchase
requisitions, or accounts receivable and payable. These will always be the
most important uses for database files.
However, there are other ways to use database files that may seem a bit
more exotic at first, but they can be very powerful. The following program
shows how a database file can be used to supply a program with variable
parameters. In this case, the database file Conditn.DBF holds the number
of different conditions on which the program is REPORTing, sparing us the
necessity of hard-coding the conditions into the program itself. If later
we want to modify or add conditions, we need only modify Conditn.DBF.
This program performs the following function. It reports from the log of
all the support calls that I have received in the last two weeks, breaking
them down by product (dBASE II, dBASE III, Framework, Friday, and other
utility programs). It uses REPORT FORM for its ease, but also does some
statistical reporting that the REPORT FORM cannot perform. Since my
database file has no numeric fields, I have no need for totals and
subtotals. However, I do want to know the count of calls for each product.
The program uses EJECT, NOEJECT, PROW(), and PCOL() to position the printer
correctly.
The structure for the database file Conditn.DBF is as follows:
Structure for database file: Conditn.DBF
Field Field Name Type Width Dec
1 Condition Character 15
** Total ** 16
It has one record for each product:
Product = 'D3'
Product = 'D2'
Product = 'FW'
Product = 'FR'
Product = 'UT'
for a total of five records.
* Program ..: Cumprint.PRG
* Author ...: Ralph Davis
* Date .....: October 1,1985
* Note .....: Prints counts from cumulative call log.
SET TALK OFF
ACCEPT " FILENAME: " TO filename
ACCEPT "FORM NAME: " TO formname
USE &filename
SELECT 2
USE Conditn
* ---EJECT page to reset line and column counters.
SET PRINT ON
EJECT
* ---Initialize control variables.
printmore = .T.
memcond = Condition
DO WHILE printmore
SELECT &filename
COUNT FOR &memcond TO mcount
IF mcount > 0
REPORT FORM &formname NOEJECT TO PRINT FOR &memcond
SET DEVICE TO PRINT
* ---Skip to next page if line counter is past 58.
IF PROW() > 58
@ 5,0
ENDIF
@ PROW()+2,1 SAY "NUMBER OF CALLS: "
@ PROW(),PCOL()+1 SAY mcount
SET DEVICE TO SCREEN
EJECT
ENDIF
* ---Return to control file.
SELECT Conditn
* ---Get next condition.
SKIP
* ---Leave program if no more conditions.
IF EOF()
EXIT
ELSE
memcond = Condition
ENDIF
ENDDO
SET PRINT OFF
SET TALK ON
RETURN
* EOP Cumprint.PRG
5 dBASE III Technical Notes
5.1 Simple List Program
dBASE III offers standard list reporting through the REPORT FORM command.
The REPORT FORM is easy to learn and very useful for column formatting
whether you use one file or several files that are linked with the SET
RELATION command. Often, however, you may find the REPORT FORM restrictive
and so find it necessary to design a custom report.
There are two fundamental reasons for this. First, there are physical
limitations to the REPORT FORM definition. Specifically, there are limits
to the number of fields and characters in a report definition. If you have
a complex or large report, you may have run into these limits. Second,
your report requires features that are not supported by the REPORT FORM.
These may include statistical calculations such as averaging, multiple
parent-child relations, formatted numeric fields, and non-columnar format,
such as a pre-printed form.In all of these cases a custom report must be
written.
The purpose of this article is to help you understand the concepts behind
writing a custom report by way of a working example. The following sample
program illustrates a method to report on two files with one to many
relationships.
The program was originally written for a school that employs sales people
to recruit students. It generates a report that details all the students
recruited for each recruiter and the current session. There are two files
used. One is Recruit.DBF and the other is Referral.DBF. The two files
contain a common field, Ref_code, a unique code assigned to each recuiter.
In the Referral.DBF file, Ref_code indicates which recuiter signed up that
particular referral. For each record in the Recruit file there may be
several associated records in the Referral file.
The Recruit database file contains information regarding the sales people:
name, address, city, state, zip, and the unique referral code (Ref_code).
The Referral database file contains student and class information: name,
school location, class level, and referral code (Ref_code), and is INDEXed
ON Ref_code TO Code.NDX. As with any program, the first part sets up a
specific environment. SET defaults are changed, the screen is CLEARed, the
necessary files are opened, and the printer is accessed. All the memory
variables utilized globally by the program are then initialized. A page
counter is set up (m_pagectr) and a line counter is established (m_line).
The line counter, m_line, is initialized to a large number so that the
first page is EJECTed. Next, a loop is established to instruct dBASE III to
process the commands until there are no more records in Recruit.DBF. This
is indicated when the EOF() function returns a true value. The idea is to
use Recruit.DBF as the point of reference into the Referral.DBF.
The next step is to find and print all the records from the Referral.DBF
file, that have the same Ref_code as the current record in Recruit.DBF.
This is done by SELECTing the second file and SEEKing Recruit->Ref_code.
Note the use of the alias name when SEEKing with a field form a
non-SELECTed work area.
A test must then be made to determine if there are any referrals for the
current recruiter. The statement IF EOF() tests for this. If there are no
referrals found by the SEEK command, EOF() returns a true (.T.). In this
example, if a record was not found, dBASE III is instructed to SELECT
Recruit.DBF, SKIP to the next record, LOOP around to the DO WHILE .NOT.
EOF() statement, and start the process again.
If a record is found, then the record pointer is positioned at the first
occurrence of Ref_code in Referral.DBF. In order to display all the
records that have the same referral code, it is necessary to enter a second
DO WHILE loop that will skip through Referral.DBF until the Ref_code in
this file no longer matches Recruit->Ref_code, or the end of the file is
reached.
The statement "IF m_line > 56" tests the line counter to determine whether
or not to eject to a new page before printing. This is where the statement
"m_line = 90" makes sense. Storing 90 to m_line every time a new record is
selected from the Recruit file ensures that the first time the statement IF
m_line > 56 is evaluated, headings will be printed since m_line is greater
than 56. Notice that right after that IF statement m_line is reset to 11,
the location where the first line of data will be printed.
After the ENDIF, the first record from the Referral.DBF is printed, the
line counter is incremented, and the record pointer SKIPs to the next
record. It is necessary to increment the line counter so that the printer
moves forward. When sending data to the printer for a custom report,
@...SAYs should move from top to bottom and left to right. If an attempt
is made to print to a row and column position that has already been printed
on the current page, then a formfeed is sent to the printer. In our
example, we use this concept to our advantage. The headings are set up to
begin printing on line 1, which requires the printer to eject to the top of
a new page if it isn't already positioned there.
dBASE III stays in this loop until all the records from Referral.DBF that
match Recruit->Ref_code are printed. Each time a new record is to be
printed, m_line is tested, and ejects to a new page when appropriate.
Once all the matched records are printed from Referral.DBF, the program
SELECTs the Recruit.DBF file, moves the record pointer to the next record,
and loops around to the first DO WHILE .NOT. EOF() to begin processing a
new record.
When all the records in Recruit.DBF have been reported on, an EJECT is sent
to be sure that the last line of data is printed. The printer is then
disabled (SET DEVICE TO SCREEN), the files are closed, and control is
RETURNed to the calling program or the dot prompt.
To test this program, add two or three records to Recruit.DBF with
Ref_codes 101 and 102. Then add a few records to Referral.DBF and include
the same Ref_codes, 101 and 102. Once this is done, INDEX Referral.DBF ON
Ref_code TO Code.NDX. To execute the program, turn the printer on and type
DO Report.
Pseudocode
1. Set Up the Working Environment.
Clear the screen.
Define environmental SETtings.
Define work areas.
Open database files with appropriate indices.
Define relations and filters.
Select the initial work area.
Initialize global variables.
Set the page counter to 0.
Set the line counter to a value greater than the page length.
Direct output to the printer.
2. Print the Report.
Process all the records in the primary work area.
Select the secondary work area.
Move to the first secondary record.
If there aren't any.
Select the primary work area.
Move to the next primary record.
Start the process over.
Process all the records in the secondary work area that match the
primary record.
If the line counter is greater than the page length
Advance the printer to a new page.
Print page heading.
Print page number and date.
Print report title.
Print field titles.
Increment the page counter.
Set the line count to the row position of the first detail
line.
Print one detail line.
Increment the line count.
Move to next secondary record.
Select the primary area
Move to next primary record.
3. Restore the Environment.
Advance the printer to a new page.
Direct output to the screen.
Close all the work areas.
Return to calling program or the dot prompt.
Report
The following command file was built from the pseudocode listed above.
* Program..: Report.PRG
* Author...: Karen A. Robinson
* Date.....: October 1, 1985
* Notes....: USEs Referral.DBF and Recruit.DBF to generate a
* custom report of student referrals by recruiter.
*
CLEAR
SET TALK OFF
* --- Open work areas.
SELECT 2
USE Referral INDEX Code
SELECT 1
USE Recruit
SET DEVICE TO PRINT
* --- Initialize memory variables for the page headings.
STORE 1 TO m_pagctr
STORE 90 TO m_line
* --- Print all recruiters and their respective referrals.
DO WHILE .NOT. EOF()
* --- Calculate column positions so that the name and address
* --- of the recruiter will be centered.
STORE 40 - LEN(TRIM(Name))/2 TO m_1
STORE 40 - LEN(TRIM(Address))/2 TO m_2
STORE 40 - ((LEN(TRIM(City)) + LEN(State) + LEN(Zip) + 4)/2);
TO m_3
* -- Find the first referral from Recruit.DBF.
SELECT Referral
SEEK Recruit->Ref_code
* --- Test for the existence of a referral.
* --- If one is not found, then get the next recruiter
* --- and start the process over.
IF EOF()
SELECT Recruit
SKIP
LOOP
ENDIF
* --- The first referral has been found. The following
* --- prints all the records from Referral.DBF that have
* --- the same Ref_code as the Ref_code in Recruit.DBF.
DO WHILE Ref_code = Recruit->Ref_code .AND. .NOT. EOF()
* --- Test for a new page. If the line count exceeds
* --- 56, a new page is required.
IF m_line > 56
* --- Print page and column headings.
@ 1, 28 SAY 'STUDENT REFERRAL REPORT'
@ 1, 65 SAY 'Page ' + STR(m_pagctr,3)
@ 2, 65 SAY 'Date '
@ 2, 72 SAY DATE()
@ 4,m_1 SAY Recruit->Name
@ 5,m_2 SAY Recruit->Address
@ 6,m_3 SAY TRIM(Recruit->City) + ', ' +;
Recruit->State + ' ' + Recruit->Zip
@ 9,14 SAY 'Level'
@ 9,23 SAY 'Center'
@ 9,43 SAY 'Student Name'
* --- Increment page and line counts.
STORE 11 TO m_line
STORE m_pagctr + 1 TO m_pagctr
ENDIF
* --- Print a referral, a record from the Referral
* --- database file.
@ m_line,14 SAY Level
@ m_line,23 SAY Center
@ m_line,43 SAY Name
* --- Increment the line counter.
STORE m_line + 1 TO m_line
* --- Get the next Referral for the current
* --- recruiter.
SKIP
ENDDO
* --- Get the next recruiter.
SELECT Recruit
SKIP
ENDDO
* --- Restore the environment and return.
EJECT
SET DEVICE TO SCREEN
CLOSE DATABASES
RETURN
* EOF Report.PRG
Conclusion
In this article, we have discussed custom reporting using Report.PRG as an
example. To recapitulate, Report.PRG utilizies several reporting
techniques. First, the program reports data from two database files that
have a common field, Ref_code. Second, a page header is centered on the top
of each page. Third, a line counter is incremented to a large number which
causes the formatted report to print on a separate page for each record
that meets a sepcified condition. You can change Report.PRG to fit your
needs, replacing filenames and field names with those of your own.
5.2 Passing Parameters to Assembly-language Subroutines
by Ralph Davis
General Considerations
With the introduction of the Developer's Release, dBASE III has acquired
the ability to pass parameters to assembly language subroutines. In
combination with the ability to LOAD and CALL the routines, you now have
the possibility of much closer linking between a dBASE program and an
assembler routine than was previously possible using the RUN command.
The dBASE III Developer's Release Reference Manual documents this feature
on pages 4-26A and 4-72A through 72C. It states that the address at which
the variable is stored is passed in DS:BX; that you can pass any type of
memory variable; that character variables terminate with the null character
(ASCII zero); and that you must not shorten or lengthen the variables that
you pass.
In this article, I will explore some techniques for passing parameters to
assembler routines from the Developer's Release of dBASE III, and make some
suggestions for the most efficient ways to do this.
How to Pass Variables
One thing you will need to understand is the information dBASE III passes
you. What form are the different variables in? What can you do with them?
What are the best ways to do so?
First of all, it is interesting to note that DS:BX points to the first byte
of data, not the first byte of the variable. The first byte of all
variables contains the length of the variable. You can obtain this easily
by decrementing BX. For character variables, the length includes the null
character string terminator at the end; therefore, the length you are
concerned with is the length descriptor minus one. All other variable
types--numeric, date, and logical--have a fixed length of eight bytes.
Each one has a different format in memory.
Secondly, although it appears that you can only pass one variable at a
time, if you are methodical, you can pass as many variables as you want.
As you might expect, variables are assigned to memory storage space
sequentially. As long as they are not RELEASEd, and the memory space
reassigned, they will be stored one right after the other. Therefore, by
passing the first variable, you can easily locate all subsequent variables
by using the length descriptors. Later in this article, I will go into
greater detail about how to do this.
Format of Variables in Memory
Memory variables are stored in the following formats:
1. Character variables are stored with the length in the first byte,
followed by the actual string, and ending with 00.
2. Numeric variables are stored in IEEE long-real format. They use
eight bytes, which appear in reverse order (most significant byte
at the highest memory address). The first bit is the sign bit: 0
for positive and 1 for negative. The next 11 bits are the exponent
(that is, the power of 2) plus 1023 decimal (3FF hexadecimal). The
final 52 bits are the mantissa, the part of the number following
the decimal point. The actual value of the number is computed as
1.<mantissa> times 2 to the exponent-1023). For instance, 2 (1.000
times 2^1) is stored as:
00 00 00 00 00 00 00 40
Negative four, -4, (-1.000 times 2^2) is stored as:
00 00 00 00 00 00 10 C0
Notice that the bits of the mantissa represent negative powers of
2, from 2^(-1) (1/2) to 2^(-52) (approximately 1 divided by 4.5
quadrillion).
3. Logical variables are stored as eight bytes, apparently to reserve
space for redefining them. All PUBLIC variables are initially
defined as logical type with a value of false until they are given
a value. The first byte is 01 for .T. and 00 for .F. This is the
only byte you need be concerned with.
4. Date variables are also stored in reverse order MSB last) in
encoded binary form.
Suggestions
Passing Multiple Variables
When you pass a variable to an assembly language subroutine, you do so by
issuing the command:
CALL <routine> WITH <variable>
For instance, if you store 'JOHN Q. PUBLIC' to mem1 and then CALL a routine
with mem1, when your routine gains control, DS:BX will be pointing to the J
in JOHN. Decrement BX to retrieve the length descriptor, in this case 15
(0F hex). To re-emphasize: the length includes the 00 which terminates all
string variables. The actual string is only 14 characters long.
If you initialize several memory variables at once, then CALL a routine
with the first one, you can use the length descriptor to locate the next
one. If, after storing 'JOHN Q. PUBLIC' to mem1, you store 'LOS ANGELES'
to mem2 and 'CA' to mem3, you can pass mem1 to your routine, and locate
mem2 and mem3 in the following manner. Decrement BX to get mem1's length.
Add it to BX. Add one to BX for the terminating descriptor. Now BX is
pointing to mem2's length descriptor. Add this value to BX and add one
again; you are now pointing to mem3's length descriptor. To pass multiple
variables in this fashion, it is critical that you initialize them one
right after the other. For example:
STORE 'JOHN Q. PUBLIC' TO mem1
STORE 'LOS ANGELES' TO mem2
STORE 'CA' TO mem3
Then you can CALL your routine and easily locate mem1, mem2, and mem3. If,
however, you RELEASE and then redefine a variable, you will not be able to
do this. The following sequence will not work:
STORE 'JOHN Q. PUBLIC' TO mem1
STORE 'LOS ANGELES' TO mem2
... <other commands not affecting memory variables>
RELEASE mem1
STORE 'CA' TO mem1
I therefore suggest that when you pass variables to subroutines, STORE all
of them immediately prior to invoking the routine. Even if the variables
have been defined elsewhere, redefine them here with new names. Then
replace the old variables upon returning from the routine. This will give
you the greatest degree of control over the variables you pass to your
routine.
Passing Different Variable Types
As I mentioned earlier, numeric variables are stored in IEEE format, and
dates are stored in an encrypted binary form. Both of these formats are
cumbersome to work with. So I recommend that you pass all date and numeric
variables as characters. There will undoubtedly be applications where you
will want the actual binary representation of numeric or date information--
ones using the 8087 or 80287, for example--and in these instances, of
course, it is appropriate to pass the variable in its actual form. But for
most purposes, it is much easier to pass the ASCII codes, then process them
as needed in your routine. Since the only part of a logical variable that
concerns us is the first byte, it is best to pass it as is. You then
return 0 for false and 1 for true. The program Printchk.ASM, listed later
in this issue, passes a logical variable, and determines program action
based on the value returned by the assembler routine.
Checking Printer Status
by Ralph Davis
Introduction
We are all human, and need not be ashamed to admit that sometimes we try to
print files when our printer is either turned off or not ready. If we do
this from dBASE II or III, we may soon be facing the DOS prompt. Neither
dBASE II nor III checks the printer before trying to print. The error
recovery is left to DOS. If the printer is off-line, DOS issues the
familiar "Write fault error writing device PRN Abort, Retry, Ignore"
message. Turning the printer on-line and pressing "R" for "Retry" resumes
the printing operation, and all goes well. Pressing "A" or "I," however,
returns us to the operating system prompt, and may cause database file
corruption.
The following assembler routine, Prntchk.ASM, checks the printer status
port for LPT1 and controls program flow according to its findings. It is
written according to the specifications outlined in the September issue of
TechNotes, with conditional assembler directives to permit the same source
code to create programs either for dBASE II, dBASE III 1.0 and 1.1, or
dBASE III Developer's Release. It functions somewhat differently in each
environment.
The program is called Prntchk rather than Printchk because of an anomaly in
the Developer's Release with the LOAD command which obliges us to name LOAD
modules with seven characters or less. Keep this in mind when naming
programs for use with dBASE III.
Discussion of Prntchk.ASM
When you use Prntchk with dBASE II or pre-Developer's Release versions of
dBASE III, you RUN it or QUIT TO it after assembling it as a .COM file.
This version of the program checks the printer status and does one of three
things:
1. Reports that the printer is available, and returns control to dBASE
II or dBASE III.
2. Reports that the printer is turned off, advises you to turn it on,
and retains control until it is turned on.
3. Reports that the printer is off-line and retains control until it
is on-line.
Notice that in cases 2 and 3, the program does not relinquish control.
After issuing its report, it waits for you to adjust the printer and press
any key to continue. It will not release control until it obtains a ready
report from the printer. Thus it assures that dBASE will not attempt a
printing operation until the printer is ready to carry it out, protecting
your files from potential data loss.
The program operates somewhat differently with the Developer's Release.
Here, it initializes a logical variable to false (.F.), then passes this
variable to Prntchk. Prntchk checks the printer status, and places either
a 0 (.F.) or a 1 (.T.) back into the variable. Upon return, your dBASE
program can test this variable and determine program flow based on it.
Here is a short section of code from a printing program that is based on
this idea.
SET TALK OFF
LOAD Prntchk
ok2print = .F.
DO WHILE .NOT. ok2print
CALL Prntchk WITH ok2print
IF .NOT. ok2print
?
? 'Printer is not ready - PLEASE CORRECT'
WAIT
ENDIF
ENDDO
The program cannot get past this DO WHILE loop until the printer is ready
for output.
.LFCOND
PAGE 60,132
;
D3DR EQU 1 ; Assemble for dBASE III,
; Developer's Release.
COM EQU 0
TRUE EQU 1 ; Define symbols
FALSE EQU 0 ; for .T. and .F.
;**************************************
CODESEG SEGMENT BYTE PUBLIC 'CODE'
ASSUME CS:CODESEG,ES:CODESEG
;--------------------------------------
PRNTCHK PROC FAR
IF COM ; ORG at 100H
ORG 100H ; for .COM file.
ENDIF
START: JMP NEXT_STEP ; Skip past data.
;
MESS1 DB 'PRINTER OFF-LINE - PLEASE ADJUST',0DH,0AH,'$'
MESS2 DB 'PRINTER NOT TURNED ON - PLEASE TURN IT ON'
DB 0DH,0AH,'$'
MESS3 DB 'PRINTER AVAILABLE FOR PRINTING',0DH,0AH,'$'
MESS4 DB 'PRESS ANY KEY TO CONTINUE',0DH,0AH,'$'
OK DB ?
;
NEXT_STEP:
PUSH AX ; Save registers.
PUSH BX
PUSH DS
PUSH ES
PUSH CS
POP ES
MOV AX,40H ; Point to system data segment.
MOV DS,AX
MOV SI,8 ; Point to LPT1 port address.
MOV DX,[SI] ; Load it into DX.
INC DX ; Point to LPT1 status port
IN AL,DX ; and read it.
CMP AL,0DFH ; Printer OK?
JNE OFF_LINE ; No, is it off-line?
MOV ES:OK,TRUE ; Place .T. in temporary variable.
IF COM
MOV DX,
OFFSET MESS3 ; Print report if .COM file.
CALL PRINTMESS
ENDIF
JMP SHORT EXIT ; Return to dBASE.
OFF_LINE:
CMP AL,0CFH ; Is printer off-line?
JNE TURNED_OFF ; No, it must be turned off.
MOV ES:OK,FALSE ; Place .F. in temporary variable.
IF COM
MOV DX,OFFSET MESS1 ; Print report if .COM file.
CALL PRINTMESS
MOV DX,OFFSET MESS4
CALL PRINTMESS
CALL CRLF ; Skip line.
POP ES
POP DS
POP BX
POP AX
CALL WAIT ; Wait for keypress.
JMP NEXT_STEP ; Go back and check status again.
ENDIF
JMP SHORT EXIT ; Leave if OK.
TURNED_OFF:
MOV ES:OK,FALSE ; Place .F. in temporary variable.
IF COM
MOV DX,OFFSET MESS2 ; Print report if .COM file.
CALL PRINTMESS
MOV DX,OFFSET MESS4
CALL PRINTMESS
CALL CRLF ; Skip line.
POP ES
POP DS
POP BX
POP AX
CALL WAIT ; Wait for keypress.
JMP NEXT_STEP ; Go back and check status again.
ENDIF
EXIT: POP ES ; Restore registers.
POP DS ; Now DS and BX are pointing
POP BX ; to variable passed by
dBASE III.
IF D3DR
MOV AL,ES:OK ; Get .T. or .F. from temporary
; variable.
MOV BYTE PTR [BX],AL ; Place it in dBASE variable.
ENDIF
POP AX ; Restore remaining registers.
IF COM
INT 20H ; INT 20H if .COM file.
ELSE
RET ; Far return if Developer's
Release.
ENDIF
;
PRNTCHK ENDP
;----------------------------------------
; SUBROUTINES
;----------------------------------------
CRLF PROC NEAR ; Skips line.
PUSH AX ; Save registers.
PUSH DX
MOV DL,0DH ; Print carriage return.
MOV AH,2
INT 21H
MOV DL,0AH ; Print line-feed.
MOV AH,2
INT 21H
POP DX ; Restore registers.
POP AX
RET ; Return to caller.
CRLF ENDP
;----------------------------------------
PRINTMESS PROC NEAR ; Print message.
PUSH AX ; Save registers.
PUSH DS
MOV AX,ES ; ES points to Prntchk's
MOV DS,AX ; data.
XOR AX,AX ; Zero AX.
MOV AH,9 ; Call DOS print string function.
INT 21H
POP DS ; Restore registers.
POP AX
RET ; Return to caller.
PRINTMESS ENDP
;----------------------------------------
WAIT PROC NEAR ; Waits for keypress.
; Does not check for Ctrl-Break.
PUSH AX ; Save register.
MOV AH,1 ; Call DOS, wait for keypres.
INT 21H ; Don't check Ctrl-Break
function.
POP AX ; Restore register.
RET ; Return to caller.
WAIT ENDP
;----------------------------------------
CODESEG ENDS
;******************************************
END START
6 dBASE III Version 1.1 Change Summary
